﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
namespace DAO
{
    public class ChiTietHoaDonDAO
    {
        public List<ChiTietHoaDonDTO> LayDanhSachChiTietHoaDon(int ma)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<ChiTietHoaDonDTO> KQ = new List<ChiTietHoaDonDTO>();
            string sql = "select * from ChiTietHoaDon where MaHD=" + ma;
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                ChiTietHoaDonDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new ChiTietHoaDonDTO();
                    cn.MaMonAn = int.Parse(dtrow["MaMonAn"].ToString());
                    cn.MaBan = dtrow["MaBan"].ToString();
                    cn.MaHD = int.Parse(dtrow["MaHD"].ToString());
                    cn.SoLuong = int.Parse(dtrow["SoLuong"].ToString());
                    cn.DonGia = int.Parse(dtrow["DonGia"].ToString());
                    cn.TongTien = cn.SoLuong * cn.DonGia;
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return KQ;
        }
        public int ThemChiTietHoaDon(ChiTietHoaDonDTO cthdDTO)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "INSERT INTO ChiTietHoaDon VALUES(" + cthdDTO.MaMonAn + ", '" + cthdDTO.MaBan + "', "+cthdDTO.MaHD+", " + cthdDTO.SoLuong + ", " + cthdDTO.DonGia + ", " + cthdDTO.TongTien + ")";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = -1;
            try
            {
                kq = cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch
            {
                conn.Close();
            }
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int CapNhatChiTietHoaDon(ChiTietHoaDonDTO cthdDTO, int MaMonCu)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Update ChiTietHoaDon set MaMonAn = "+cthdDTO.MaMonAn+", SoLuong=" + cthdDTO.SoLuong + ", DonGia=" + cthdDTO.DonGia + ", TongTien = "+cthdDTO.TongTien+" where MaHD=" + cthdDTO.MaHD +" and MaMonAn = "+MaMonCu+" and MaBan= '"+cthdDTO.MaBan+"'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int LayMaHDChiTietHoaDon(string MaBan)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select MaHD from HoaDon where TinhTrangHD like 'C%' and convert(varchar, GioVao, 101) = '" + DateTime.Parse(DateTime.Now.ToString()).ToString("MM/dd/yyyy") + "' and MaHD in (select MaHD from ChiTietHoaDon where MaBan = '" + MaBan + "')";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
         
        
        public int KiemTraTrungMa(ChiTietHoaDonDTO ct)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select count(*) from ChiTietHoaDon where MaHD = "+ct.MaHD+" and MaBan = '"+ct.MaBan+"' and MaMonAn = "+ct.MaMonAn;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
        public int CapNhatMaBanChiTietHoaDon(string MaBanCu, string MaBanMoi)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Update ChiTietHoaDon set MaBan='" + MaBanMoi + "' where MaBan='" + MaBanCu + "' and MaHD in (select MaHD from HoaDon where convert(varchar, GioVao, 101) = '" + DateTime.Parse(DateTime.Now.ToString()).ToString("MM/dd/yyyy") + "' and TinhTrangHD like 'C%')";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int CapNhatMaBanTheoHD(ChiTietHoaDonDTO BanCu, ChiTietHoaDonDTO BanMoi)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Update ChiTietHoaDon set MaBan='" + BanMoi.MaBan + "', MaHD= "+BanMoi.MaHD+" where MaBan='" + BanCu.MaBan + "' and MaHD = "+BanCu.MaHD+" and MaMonAn="+BanCu.MaMonAn;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int XoaChiTietHoaDon(ChiTietHoaDonDTO cthd)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Delete ChiTietHoaDon where MaHD=" + cthd.MaHD +" and MaBan = '"+cthd.MaBan+"' and MaMonAn= " + cthd.MaMonAn;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int XoaChiTietHoaDon(int mahd)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Delete ChiTietHoaDon where MaHD=" + mahd;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public float TinhTongTienChiTietHoaDon(int MaHD)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select sum(TongTien) from ChiTietHoaDon where MaHD = " + MaHD;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            float kq;
            try
            {
                kq = float.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
        public List<ChiTietHoaDonDTO> LayMaMonAnBietMaHD_MaBan(ChiTietHoaDonDTO cthd)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select * from ChiTietHoaDon where MaHD = " + cthd.MaHD+" and MaBan = '"+cthd.MaBan+"'";
            List<ChiTietHoaDonDTO> KQ = new List<ChiTietHoaDonDTO>();
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                ChiTietHoaDonDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new ChiTietHoaDonDTO();
                    cn.MaMonAn = int.Parse(dtrow["MaMonAn"].ToString());
                    cn.MaBan = dtrow["MaBan"].ToString();
                    cn.MaHD = int.Parse(dtrow["MaHD"].ToString());
                    cn.SoLuong = int.Parse(dtrow["SoLuong"].ToString());
                    cn.DonGia = int.Parse(dtrow["DonGia"].ToString());
                    cn.TongTien = cn.SoLuong * cn.DonGia;
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return KQ; 
        }
        public int LaySoLuongBietMaHD_MaBan_MaMonAn(ChiTietHoaDonDTO cthd)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select SoLuong from ChiTietHoaDon where MaHD = " + cthd.MaHD + " and MaBan = '" + cthd.MaBan + "' and MaMonAn = " + cthd.MaMonAn;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }

        public int LayMaHDChiTietHoaDonDam(string MaBan)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select MaHD from HoaDon where TinhTrangHD like 'C%' and MaHD in (select MaHD from ChiTietHoaDon where MaBan = '" + MaBan + "')";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
        public int DemSoLuongChiTietHoaDon(int MaHD)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select count(*) from ChiTietHoaDon where MaHD=" + MaHD;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            return kq;
        }
    }
}
